import sqlite3
import re
from typing import Optional, List, Dict, Any
from queue import Queue

class DatabaseManager:
    def __init__(self, db_path):
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row 
        self.cursor = self.conn.cursor()

# region 宠物大致信息
    def search_pets(self, id: Optional[int] = None, 
                   name: Optional[str] = None, 
                   attribute: Optional[str] = None,
                   now_id: int = 0,
                   all_pets:bool=False) -> List[Dict]:
        """搜索宠物信息
        参数:
            pet_id (int): 精确ID查询
            name (str): 名称模糊查询
            attribute (str): 属性模糊查询
            now_id (int): 起始ID查询
            limit (int): 返回结果限制数
            
        返回:
            list: 包含字典的列表，每个字典包含处理后的宠物信息
        """
        conditions = []
        values = []
        if name:
            conditions.append("name LIKE ?")
            values.append(f"%{name}%")
        if id:
            conditions.append("id = ?")
            values.append(id)
        if attribute:
            conditions.append("attribute LIKE ?")
            values.append(f"%{attribute}%")
        if now_id:
            conditions.append("id >= ?")
            values.append(now_id)

        if conditions:
            condition_str = " AND ".join(conditions)
            if all_pets:
                query = f"SELECT name, id, bloodline, attribute FROM rock_pet_info WHERE {condition_str}"
            else:
                query = f"SELECT name, id, bloodline, attribute FROM rock_pet_info WHERE {condition_str} LIMIT 8"
        else:
            if all_pets:
                query = "SELECT name, id, bloodline, attribute FROM rock_pet_info"
            else:
                query = "SELECT name, id, bloodline, attribute FROM rock_pet_info LIMIT 8"

        # 执行查询
        try:
            self.cursor.execute(query, values)
            pet_infos = self.cursor.fetchall()
            return pet_infos

        except AttributeError as e:
            print(f"错误: {e}，self.cursor 可能被错误赋值。")
            return
# endregion

# region 宠物详细信息
    def get_creature_by_id(self, creature_id: int) -> Optional[Dict[str, Any]]:
        """
        通过ID获取完整宠物数据
        参数:
            creature_id (int): 要查询的宠物ID
        返回:
            Dict: 包含全部字段的生物数据字典，找不到返回None
        """
        query = """
            SELECT * FROM rock_pet_info WHERE id = ?
        """
        try:
            self.cursor.execute(query, (creature_id,))
            result = self.cursor.fetchone()
            return dict(result) if result else None
        except sqlite3.Error as e:
            print(f"数据库查询错误: {e}")
            return None
# endregion

# region 技能信息
    def get_pet_skills(self, pet_id: int) -> List[Dict[str, Any]]:
        """
        根据宠物ID获取完整技能信息
        
        参数:
            pet_id (int): 要查询的宠物ID
            
        返回:
            list: 包含技能字典的列表，格式示例:
        """
        query = """
        SELECT 
            s.name, 
            s.type,
            s.power,
            s.pp,
            s.description,
            ps.level
        FROM rock_pet_skill ps
        JOIN rock_all_skills s 
            ON ps.skill_id = s.skill_id
        WHERE ps.pet_id = ?
        ORDER BY CAST(ps.level AS INTEGER) ASC  -- 按学习等级数字排序
        """
        
        try:
            self.cursor.execute(query, (pet_id,))
            return [dict(row) for row in self.cursor.fetchall()]
        
        except sqlite3.Error as e:
            print(f"技能查询错误: {e}")
            return []
        except Exception as e:
            print(f"未知错误: {e}")
            return []
# endregion

# region 获取战斗策略
    def get_battle_strategies(self, opponent_pet_name: str, our_pet_team: List[str]) -> Queue:
        """
        根据对手宠物名和我方宠物队伍获取战斗策略数据
        参数:
            opponent_pet_name (str): 对手宠物的名字
            our_pet_team (List[str]): 我方宠物队伍，包含六只宠物名字
        返回:
            Queue: 包含战斗策略数据的队列，每个元素为 (标志位, 数据字典) 元组
        """
        strategies_queue = Queue()
        
        for our_pet_name in our_pet_team:
            # print(f"🔍 获取战斗策略数据：{our_pet_name} vs {opponent_pet_name}")
            query = """
            SELECT * FROM rock_battle_strategies 
            WHERE opponent_pet_name = ? AND our_pet_name = ?
            """
            try:
                self.cursor.execute(query, (opponent_pet_name, our_pet_name))
                results = self.cursor.fetchall()
                if results:
                    for row in results:
                        strategies_queue.put((1, dict(row)))  # 标志位 1 表示同时满足两个条件
                else:
                    continue
            except sqlite3.Error as e:
                print(f"数据库查询错误: {e}")

        for our_pet_name in our_pet_team:
            query = """
            SELECT * FROM rock_battle_strategies 
            WHERE opponent_pet_name = ? AND our_pet_name = ?
            """
            try:
                self.cursor.execute(query, (our_pet_name,opponent_pet_name))
                results = self.cursor.fetchall()
                if results:
                    for row in results:
                        strategies_queue.put((2, dict(row)))  # 标志位 2 表示同时满足两个条件
                else:
                    continue
            except sqlite3.Error as e:
                print(f"数据库查询错误: {e}")

        # 再获取仅与 opponent_pet_name 相关的数据
        query = """
        SELECT * FROM rock_battle_strategies 
        WHERE opponent_pet_name = ?
        """
        try:
            self.cursor.execute(query, (opponent_pet_name,))
            results = self.cursor.fetchall()
            for row in results:
                strategies_queue.put((3, dict(row)))  # 标志位 3 表示仅满足 opponent_pet_name 条件
        except sqlite3.Error as e:
            print(f"数据库查询错误: {e}")

        return strategies_queue
# endregion

# region 输入宠物信息
    def insert_pet_data(self, data):
        """插入完整的宠物数据（基本信息、技能及关联）
        
        参数:
            data (dict): 包含宠物信息及技能列表的字典
            
        异常:
            sqlite3.Error: 数据库操作异常时抛出
        """
        try:
            self.conn.execute("BEGIN TRANSACTION")
            
            # 插入宠物基本信息
            pet_info = (
                data["编号"],
                data["宠物名字"],
                data["属性"],
                data["被动免疫"],
                data["血脉"],
                data["种族值"]["精力"],
                data["种族值"]["物攻"],
                data["种族值"]["物防"],
                data["种族值"]["魔攻"],
                data["种族值"]["魔防"],
                data["种族值"]["速度"],
                data["种族值"]["总和"],
                data["种族值极限"]["精力"],
                data["种族值极限"]["物攻"],
                data["种族值极限"]["物防"],
                data["种族值极限"]["魔攻"],
                data["种族值极限"]["魔防"],
                data["种族值极限"]["速度"]
            )
            self.cursor.execute('''
                INSERT OR IGNORE INTO rock_pet_info (
                    id, name, attribute, passive_immunity, bloodline, 
                    hp, attack, defense, sp_attack, sp_defense, speed, total,
                    hp_max, attack_max, defense_max, sp_attack_max, sp_defense_max, speed_max
                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ''', pet_info)

            # 处理技能数据
            for skill in data["技能及其描述"]:
                # 插入技能基本信息
                skill_data = (
                    skill["名称"],
                    skill["类型"],
                    skill["威力"],
                    skill["PP"],
                    skill["描述"]
                )
                self.cursor.execute('''
                    INSERT OR IGNORE INTO rock_all_skills 
                    (name, type, power, pp, description)
                    VALUES (?,?,?,?,?)
                ''', skill_data)

                # 查询技能ID（新插入或已存在）
                self.cursor.execute('''
                    SELECT skill_id FROM rock_all_skills 
                    WHERE name=? AND type=? AND power=? AND pp=? AND description=?
                ''', skill_data)
                skill_id = self.cursor.fetchone()[0]

                # 插入关联关系
                pet_skill_data = (
                    data["编号"],
                    skill_id,
                    skill["等级"]
                )
                self.cursor.execute('''
                    INSERT OR IGNORE INTO rock_pet_skill 
                    (pet_id, skill_id, level) 
                    VALUES (?,?,?)
                ''', pet_skill_data)

            self.conn.commit()
            
        except sqlite3.Error as e:
            self.conn.rollback()
            raise sqlite3.Error(f"数据库操作失败: {str(e)}")
        except Exception as e:
            self.conn.rollback()
            raise RuntimeError(f"系统错误: {str(e)}")
# endregion

# region 输入战斗策略
    def insert_battle_strategy(self, 
                            opponent_pet_name: str,
                            our_pet_name: str,
                            turn_order: int,
                            outcome: str,
                            strategy_description: str,
                            success_count: int):
        """
        插入或更新战斗策略数据到rock_battle_strategies表
        
        参数:
            opponent_pet_name (str): 对手宠物名
            our_pet_name (str): 我方宠物名
            turn_order (int): 上场顺序（-1:后发，0:同时，1:先发）
            outcome (str): 战斗结果描述
            strategy_description (str): 策略详细描述
            success_count (int): 成功次数统计
            
        异常:
            sqlite3.Error: 数据库操作异常时抛出
        """
        try:
            self.conn.execute("BEGIN TRANSACTION")
            
            # 检查是否已存在相同记录 [1,3](@ref)
            self.cursor.execute('''
                SELECT success_count FROM rock_battle_strategies 
                WHERE opponent_pet_name=? AND our_pet_name=?
            ''', (opponent_pet_name, our_pet_name))
            
            existing_record = self.cursor.fetchone()
            
            if existing_record:
                # 存在记录：更新success_count（原值+1）[1,11](@ref)
                new_success_count = existing_record[0] + 1
                update_data = (
                    turn_order,
                    outcome,
                    strategy_description,
                    new_success_count,
                    opponent_pet_name,
                    our_pet_name
                )
                
                self.cursor.execute('''
                    UPDATE rock_battle_strategies 
                    SET turn_order=?, outcome=?, strategy_description=?, success_count=?
                    WHERE opponent_pet_name=? AND our_pet_name=?
                ''', update_data)
            else:
                # 不存在记录：插入新数据 [1,4](@ref)
                strategy_data = (
                    opponent_pet_name,
                    our_pet_name,
                    turn_order,
                    outcome,
                    strategy_description,
                    success_count
                )
                
                self.cursor.execute('''
                    INSERT INTO rock_battle_strategies 
                    (opponent_pet_name, our_pet_name, turn_order, outcome, strategy_description, success_count)
                    VALUES (?,?,?,?,?,?)
                ''', strategy_data)
            
            self.conn.commit()
            
        except sqlite3.Error as e:
            self.conn.rollback()
            raise sqlite3.Error(f"战斗策略操作失败: {str(e)}")
        except Exception as e:
            self.conn.rollback()
            raise RuntimeError(f"系统错误: {str(e)}")
# endregion